<?php
/**
 * @filename sql.php
 * @encoding UTF-8
 * @author mahuaer <mahuaer@126.com, 545817057@qq.com>
 * @link http://9891.com
 * @copyright Copyright (C) 2015 玄翱网络9891
 * @license http://www.9891.com/licenses/
 * @datetime 2015-8-12  17:54:54
 * @version 1.0
 * @Description
 */
/**
 * 生成mysql数据字典
 */
header("Content-type: text/html; charset=utf-8");
// 配置数据库
$dbserver = "127.0.0.1";
$dbport = "3306";
$dbusername = "root";
$dbpassword = "root";
$database = "liansuobang";
// 其他配置
$title = "数据库{$database}数据字典";
$mysql_conn = @mysql_connect("$dbserver:$dbport", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
mysql_select_db($database, $mysql_conn);
mysql_query('SET NAMES utf8', $mysql_conn);
$table_result = mysql_query('show tables', $mysql_conn);
if ($table_result) {
    // 取得所有的表名
    while ($row = mysql_fetch_array($table_result)) {
        $tables [] ['TABLE_NAME'] = $row [0];
    }
// 循环取得所有表的备注及表中列消息
    foreach ($tables as $k => $v) {
        $sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ' . "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
        $table_result = mysql_query($sql, $mysql_conn);
        while ($t = mysql_fetch_array($table_result)) {
//        var_dump($t);
            $tables [$k] ['TABLE_COMMENT'] = $t ['TABLE_COMMENT'];
            $tables [$k] ['ENGINE'] = $t ['ENGINE'];
        }
        $sql = 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ' . "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
        $fields = array();
        $field_result = mysql_query($sql, $mysql_conn);
        $trStrs = '';
        while ($f = mysql_fetch_array($field_result)) {
            $trStrs .= '<tr><td class="c1">' . $f ['COLUMN_NAME'] . '</td>';
            $trStrs .= '<td class="c2">' . $f ['COLUMN_TYPE'] . '</td>';
            $trStrs .= '<td class="c3">' . $f ['COLUMN_DEFAULT'] . '</td>';
            $trStrs .= '<td class="c4">' . $f ['IS_NULLABLE'] . '</td>';
            $trStrs .= '<td class="c5">' . ($f ['EXTRA'] == 'auto_increment' ? '是' : '&nbsp;') . '</td>';
            $trStrs .= '<td class="c6">' . $f ['COLUMN_COMMENT'] . ($f['COLUMN_KEY'] == 'PRI' ? '(主键)' : ($f['COLUMN_KEY'] == 'MUL' ? '(索引)' : '')) . '</td>';
            $trStrs .= '</tr>' . PHP_EOL;
        }
        $tables [$k] ['COLUMN'] = $trStrs;
    }
    mysql_close($mysql_conn);
    $html = '';
    $maodian = '<div class="maodian"><table><tr><th></th><th>表名</th><th>注释</th></tr>'; //锚点
// 循环所有表
    foreach ($tables as $k => $v) {
//         $html .= '<p><h2>'. $v['TABLE_COMMENT'] . '&nbsp;</h2>';
        $html .= '<a name="' . $v ['TABLE_NAME'] . '"></a><table  border="1" cellspacing="0" cellpadding="0" align="center">';
        $html .= '<caption>' . ($k + 1) . '. ' . $v ['TABLE_NAME'] . '&nbsp;&nbsp;&nbsp;' . $v ['TABLE_COMMENT'] . '&nbsp;&nbsp;&nbsp;' . $v['ENGINE'] . '</caption>';
        $html .= '<tbody><tr><th>字段名</th><th>数据类型</th><th>默认值</th> <th>允许非空</th> <th>自动递增</th><th>备注</th></tr>';
        $html .= PHP_EOL;
        $maodian .= '<tr><td>' . ($k + 1) . '</td><td><a href="#' . $v ['TABLE_NAME'] . '">' . $v ['TABLE_NAME'] . '</a></td>'
            . '<td class="comment">' . $v ['TABLE_COMMENT'] . '</td></tr>' . PHP_EOL;
        $html .= $v ['COLUMN'];
        $html .= '</tbody></table>' . PHP_EOL;
    }
    $maodian .= '</table></div>'; //锚点
} else {
    $html = '没有数据表';
    $maodian = '';
}

?>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html;charset = utf-8"/>
    <title><?php echo $title; ?></title>
    <style>
        body, td, th {
            font-family: "宋体", serif;
            font-size: 12px;
        }

        .main {
            position: relative;
            left: -100px; /*float:left*/
        }

        table {
            border-collapse: collapse;
            border: 1px solid #CCC;
            background: #6089D4;
        }

        table caption {
            text-align: left;
            background-color: #fff;
            line-height: 2em;
            font-size: 14px;
            font-weight: bold;
        }

        table th {
            text-align: left;
            font-weight: bold;
            height: 26px;
            line-height: 25px;
            font-size: 16px;
            border: 2px solid #fff;
            color: #ffffff;
            padding: 2px;
        }

        table td {
            height: 25px;
            font-size: 12px;
            border: 2px solid #fff;
            background-color: #f0f0f0;
            padding: 2px;
        }

        .c1 {
            width: 150px;
        }

        .c2 {
            width: 130px;
        }

        .c3 {
            width: 70px;
        }

        .c4 {
            width: 80px;
        }

        .c5 {
            width: 80px;
        }

        .c6 {
            width: 300px;
        }

        a {
            text-decoration: none
        }

        .maodian {
            position: fixed;
            padding: 1px;
            max-width: 520px; /*min-width:440px;*/
            height: 902px;
            top: 10px;
            right: 10px;
            font-size: 14px;
            overflow: auto;
            border: 1px solid red
        }

        .maodian table {
            float: right;
        }

        .maodian a {
            font-size: 14px;
            margin: 3px;
            color: #0F7104;
        }

        .maodian a:hover {
            margin: 3px;
            text-decoration: underline;
        }

        .maodian table th {
            height: 16px;
            border: 1px solid #fff;
            padding: 0;
            line-height: 16px;
            font-size: 12px;
        }

        .maodian table td {
            height: 16px;
            border: 1px solid #fff;
            padding: 0;
        }

        /*.comment{width: 160px}*/
    </style>
</head>
<body>
<div class="main">
    <?php
    echo '<h1 style="text-align:center;">' . $title . '</h1>';
    echo $html;
    echo $maodian;
    ?>
</div>
</body>
</html>
